set hive.exec.dynamic.partition=true;
set hive.exec.dynamic.partition.mode=nonstrict;
set hive.exec.max.dynamic.partitions=1000;
set hive.exec.max.dynamic.partitions.pernode=1000;
insert overwrite table jms_dm.dm_customer_volume_flow_direction_city_dt partition(dt)
select
    detail.start_time --揽收日期
    ,start_network.manage_code as manage_region_code --管理大区code
    ,start_network.manage_name as manage_region_name --管理大区name
    ,start_network.agent_code                        --代理区code
    ,start_network.agent_name                        --代理区name
    ,start_network.fran_code                         --加盟商code
    ,start_network.fran_name                         --加盟商name
    ,detail.pick_network_code                        --寄件网点名称code
    ,detail.pick_network_name                        --寄件网点名称name
    ,detail.customer_code                            --寄件客户编码
    ,detail.customer_name                            --寄件客户名称
    ,start_network.provider_id                       --寄件省份id
    ,start_network.provider_desc                     --寄件省份name
    ,start_network.city_id                           --寄件城市id
    ,start_network.city_desc                         --寄件城市name
    ,detail.receiver_province_id                     --目的省份id
    ,end_network.provider_desc                       --目的省份name
    ,detail.receiver_city_id                         --目的城市id
    ,end_network.city_desc                           --目的城市name
    ,detail.total_weight                             --重量
    ,detail.waybill_no_count                         --件量
    ,detail.one_waybill_count                        --0-0.1KG
    ,detail.one_waybill_persent                      --0-0.1KG
    ,detail.two_waybill_count                        --0.1-0.2KG
    ,detail.two_waybill_persent                      --0.1-0.2KG
    ,detail.three_waybill_count                      --0.2-0.3KG
    ,detail.three_waybill_persent                    --0.2-0.3KG
    ,detail.four_waybill_count                       --0.3-0.4KG
    ,detail.four_waybill_persent                     --0.3-0.4KG
    ,detail.five_waybill_count                       --0.4-0.5KG
    ,detail.five_waybill_persent                     --0.4-0.5KG
    ,detail.six_waybill_count                        --0.5-0.7KG
    ,detail.six_waybill_persent                      --0.5-0.7KG
    ,detail.seven_waybill_count                      --0.7-1.0KG
    ,detail.seven_waybill_persent                    --0.7-1.0KG
    ,detail.eight_waybill_count                      --1.0-1.5KG
    ,detail.eight_waybill_persent                    --1.0-1.5KG
    ,detail.nine_waybill_count                       --1.5-2.0KG
    ,detail.nine_waybill_persent                     --1.5-2.0KG
    ,detail.ten_waybill_count                        --2.0-3.0KG
    ,detail.ten_waybill_persent                      --2.0-3.0KG
    ,detail.eleven_waybill_count                     --3.0-5.0KG
    ,detail.eleven_waybill_persent                   --3.0-5.0KG
    ,detail.twelve_waybill_count                     --5.0-10.0KG
    ,detail.twelve_waybill_persent                   --5.0-10.0KG
    ,detail.thirteen_waybill_count                   --0.0-20.0KG
    ,detail.thirteen_waybill_persent                 --0.0-20.0KG
    ,detail.fourteen_waybill_count                   --≥20KG
    ,detail.fourteen_waybill_persent                 --≥20KG
    ,detail.start_time as dt                         --分区
from
    (
        select
            date(input_time) as start_time
            ,pick_network_code
            ,pick_network_name
            ,customer_code
            ,customer_name
            ,receiver_province_id
            ,receiver_city_id
            ,sum(package_inside_charge_weight) as total_weight
            ,count(aa.waybill_no) as waybill_no_count
            ,count(if(package_inside_charge_weight>=0 and package_inside_charge_weight<=0.1,aa.waybill_no,null)) as one_waybill_count
            ,count(if(package_inside_charge_weight>=0 and package_inside_charge_weight<=0.1,aa.waybill_no,null))/count(aa.waybill_no) as one_waybill_persent
            ,count(if(package_inside_charge_weight>0.1 and package_inside_charge_weight<=0.2,aa.waybill_no,null)) as two_waybill_count
            ,count(if(package_inside_charge_weight>0.1 and package_inside_charge_weight<=0.2,aa.waybill_no,null))/count(aa.waybill_no) as two_waybill_persent
            ,count(if(package_inside_charge_weight>0.2 and package_inside_charge_weight<=0.3,aa.waybill_no,null)) as three_waybill_count
            ,count(if(package_inside_charge_weight>0.2 and package_inside_charge_weight<=0.3,aa.waybill_no,null))/count(aa.waybill_no) as three_waybill_persent
            ,count(if(package_inside_charge_weight>0.3 and package_inside_charge_weight<=0.4,aa.waybill_no,null)) as four_waybill_count
            ,count(if(package_inside_charge_weight>0.3 and package_inside_charge_weight<=0.4,aa.waybill_no,null))/count(aa.waybill_no) as four_waybill_persent
            ,count(if(package_inside_charge_weight>0.4 and package_inside_charge_weight<=0.5,aa.waybill_no,null)) as five_waybill_count
            ,count(if(package_inside_charge_weight>0.4 and package_inside_charge_weight<=0.5,aa.waybill_no,null))/count(aa.waybill_no) as five_waybill_persent
            ,count(if(package_inside_charge_weight>0.5 and package_inside_charge_weight<=0.7,aa.waybill_no,null)) as six_waybill_count
            ,count(if(package_inside_charge_weight>0.5 and package_inside_charge_weight<=0.7,aa.waybill_no,null))/count(aa.waybill_no) as six_waybill_persent
            ,count(if(package_inside_charge_weight>0.7 and package_inside_charge_weight<=1,aa.waybill_no,null)) as seven_waybill_count
            ,count(if(package_inside_charge_weight>0.7 and package_inside_charge_weight<=1,aa.waybill_no,null))/count(aa.waybill_no) as seven_waybill_persent
            ,count(if(package_inside_charge_weight>1 and package_inside_charge_weight<=1.5,aa.waybill_no,null)) as eight_waybill_count
            ,count(if(package_inside_charge_weight>1 and package_inside_charge_weight<=1.5,aa.waybill_no,null))/count(aa.waybill_no) as eight_waybill_persent
            ,count(if(package_inside_charge_weight>1.5 and package_inside_charge_weight<=2,aa.waybill_no,null)) as nine_waybill_count
            ,count(if(package_inside_charge_weight>1.5 and package_inside_charge_weight<=2,aa.waybill_no,null))/count(aa.waybill_no) as nine_waybill_persent
            ,count(if(package_inside_charge_weight>2 and package_inside_charge_weight<=3,aa.waybill_no,null)) as ten_waybill_count
            ,count(if(package_inside_charge_weight>2 and package_inside_charge_weight<=3,aa.waybill_no,null))/count(aa.waybill_no) as ten_waybill_persent
            ,count(if(package_inside_charge_weight>3 and package_inside_charge_weight<=5,aa.waybill_no,null)) as eleven_waybill_count
            ,count(if(package_inside_charge_weight>3 and package_inside_charge_weight<=5,aa.waybill_no,null))/count(aa.waybill_no) as eleven_waybill_persent
            ,count(if(package_inside_charge_weight>5 and package_inside_charge_weight<=10,aa.waybill_no,null)) as twelve_waybill_count
            ,count(if(package_inside_charge_weight>5 and package_inside_charge_weight<=10,aa.waybill_no,null))/count(aa.waybill_no) as twelve_waybill_persent
            ,count(if(package_inside_charge_weight>10 and package_inside_charge_weight<=20,aa.waybill_no,null)) as thirteen_waybill_count
            ,count(if(package_inside_charge_weight>10 and package_inside_charge_weight<=20,aa.waybill_no,null))/count(aa.waybill_no) as thirteen_waybill_persent
            ,count(if(package_inside_charge_weight>20,aa.waybill_no,null)) as fourteen_waybill_count
            ,count(if(package_inside_charge_weight>20,aa.waybill_no,null))/count(aa.waybill_no) as fourteen_waybill_persent
        from jms_dwd.dwd_yl_oms_oms_waybill_incre_dt aa
        where dt >=date_add('{{ execution_date | cst_ds }}',-6) and dt<='{{ execution_date | cst_ds }}' and is_delete =1
        group by
             date(input_time)
            ,pick_network_code
            ,pick_network_name
            ,customer_code
            ,customer_name
            ,receiver_province_id
            ,receiver_city_id
    ) detail
left join jms_dim.dim_network_whole_massage start_network on detail.pick_network_code = start_network.code
left join (select provider_id,provider_desc,city_id,city_desc from jms_dim.dim_network_whole_massage group by provider_id,provider_desc,city_id,city_desc) end_network on detail.receiver_province_id =end_network.provider_id
and  detail.receiver_city_id=end_network.city_id;

insert overwrite table jms_dm.dm_customer_volume_flow_direction_province_dt partition(dt)
select
     detail.start_time --揽收日期
     ,start_network.manage_code as manage_code --管理大区code
     ,start_network.manage_name as manage_name --管理大区name
     ,start_network.agent_code                 --代理区code
     ,start_network.agent_name                 --代理区name
     ,start_network.fran_code                  --加盟商code
     ,start_network.fran_name                  --加盟商name
     ,detail.pick_network_code                 --寄件网点名称code
     ,detail.pick_network_name                 --寄件网点名称name
     ,detail.customer_code                     --寄件客户编码
     ,detail.customer_name                     --寄件客户名称
     ,start_network.provider_id                --寄件省份id
     ,start_network.provider_desc              --寄件省份name
     ,detail.receiver_province_id              --目的省份id
     ,end_network.provider_desc                --目的省份name
     ,detail.total_weight                      --重量
     ,detail.waybill_no_count                  --件量
     ,detail.one_waybill_count                 --0-0.1KG
     ,detail.one_waybill_persent               --0-0.1KG
     ,detail.two_waybill_count                 --0.1-0.2KG
     ,detail.two_waybill_persent               --0.1-0.2KG
     ,detail.three_waybill_count               --0.2-0.3KG
     ,detail.three_waybill_persent             --0.2-0.3KG
     ,detail.four_waybill_count                --0.3-0.4KG
     ,detail.four_waybill_persent              --0.3-0.4KG
     ,detail.five_waybill_count                --0.4-0.5KG
     ,detail.five_waybill_persent              --0.4-0.5KG
     ,detail.six_waybill_count                 --0.5-0.7KG
     ,detail.six_waybill_persent               --0.5-0.7KG
     ,detail.seven_waybill_count               --0.7-1.0KG
     ,detail.seven_waybill_persent             --0.7-1.0KG
     ,detail.eight_waybill_count               --1.0-1.5KG
     ,detail.eight_waybill_persent             --1.0-1.5KG
     ,detail.nine_waybill_count                --1.5-2.0KG
     ,detail.nine_waybill_persent              --1.5-2.0KG
     ,detail.ten_waybill_count                 --2.0-3.0KG
     ,detail.ten_waybill_persent               --2.0-3.0KG
     ,detail.eleven_waybill_count              --3.0-5.0KG
     ,detail.eleven_waybill_persent            --3.0-5.0KG
     ,detail.twelve_waybill_count              --5.0-10.0KG
     ,detail.twelve_waybill_persent            --5.0-10.0KG
     ,detail.thirteen_waybill_count            --0.0-20.0KG
     ,detail.thirteen_waybill_persent          --0.0-20.0KG
     ,detail.fourteen_waybill_count            --≥20KG
     ,detail.fourteen_waybill_persent          --≥20KG
     ,detail.start_time as dt                  --分区detail.start_time --揽收日期
from
    (
    select
        date(input_time) as start_time
       ,pick_network_code
       ,pick_network_name
       ,customer_code
       ,customer_name
       ,receiver_province_id
       ,sum(package_inside_charge_weight) as total_weight
       ,count(aa.waybill_no) as waybill_no_count
       ,count(if(package_inside_charge_weight>=0 and package_inside_charge_weight<=0.1,aa.waybill_no,null)) as one_waybill_count
       ,count(if(package_inside_charge_weight>=0 and package_inside_charge_weight<=0.1,aa.waybill_no,null))/count(aa.waybill_no) as one_waybill_persent
       ,count(if(package_inside_charge_weight>0.1 and package_inside_charge_weight<=0.2,aa.waybill_no,null)) as two_waybill_count
       ,count(if(package_inside_charge_weight>0.1 and package_inside_charge_weight<=0.2,aa.waybill_no,null))/count(aa.waybill_no) as two_waybill_persent
       ,count(if(package_inside_charge_weight>0.2 and package_inside_charge_weight<=0.3,aa.waybill_no,null)) as three_waybill_count
       ,count(if(package_inside_charge_weight>0.2 and package_inside_charge_weight<=0.3,aa.waybill_no,null))/count(aa.waybill_no) as three_waybill_persent
       ,count(if(package_inside_charge_weight>0.3 and package_inside_charge_weight<=0.4,aa.waybill_no,null)) as four_waybill_count
       ,count(if(package_inside_charge_weight>0.3 and package_inside_charge_weight<=0.4,aa.waybill_no,null))/count(aa.waybill_no) as four_waybill_persent
       ,count(if(package_inside_charge_weight>0.4 and package_inside_charge_weight<=0.5,aa.waybill_no,null)) as five_waybill_count
       ,count(if(package_inside_charge_weight>0.4 and package_inside_charge_weight<=0.5,aa.waybill_no,null))/count(aa.waybill_no) as five_waybill_persent
       ,count(if(package_inside_charge_weight>0.5 and package_inside_charge_weight<=0.7,aa.waybill_no,null)) as six_waybill_count
       ,count(if(package_inside_charge_weight>0.5 and package_inside_charge_weight<=0.7,aa.waybill_no,null))/count(aa.waybill_no) as six_waybill_persent
       ,count(if(package_inside_charge_weight>0.7 and package_inside_charge_weight<=1,aa.waybill_no,null)) as seven_waybill_count
       ,count(if(package_inside_charge_weight>0.7 and package_inside_charge_weight<=1,aa.waybill_no,null))/count(aa.waybill_no) as seven_waybill_persent
       ,count(if(package_inside_charge_weight>1 and package_inside_charge_weight<=1.5,aa.waybill_no,null)) as eight_waybill_count
       ,count(if(package_inside_charge_weight>1 and package_inside_charge_weight<=1.5,aa.waybill_no,null))/count(aa.waybill_no) as eight_waybill_persent
       ,count(if(package_inside_charge_weight>1.5 and package_inside_charge_weight<=2,aa.waybill_no,null)) as nine_waybill_count
       ,count(if(package_inside_charge_weight>1.5 and package_inside_charge_weight<=2,aa.waybill_no,null))/count(aa.waybill_no) as nine_waybill_persent
       ,count(if(package_inside_charge_weight>2 and package_inside_charge_weight<=3,aa.waybill_no,null)) as ten_waybill_count
       ,count(if(package_inside_charge_weight>2 and package_inside_charge_weight<=3,aa.waybill_no,null))/count(aa.waybill_no) as ten_waybill_persent
       ,count(if(package_inside_charge_weight>3 and package_inside_charge_weight<=5,aa.waybill_no,null)) as eleven_waybill_count
       ,count(if(package_inside_charge_weight>3 and package_inside_charge_weight<=5,aa.waybill_no,null))/count(aa.waybill_no) as eleven_waybill_persent
       ,count(if(package_inside_charge_weight>5 and package_inside_charge_weight<=10,aa.waybill_no,null)) as twelve_waybill_count
       ,count(if(package_inside_charge_weight>5 and package_inside_charge_weight<=10,aa.waybill_no,null))/count(aa.waybill_no) as twelve_waybill_persent
       ,count(if(package_inside_charge_weight>10 and package_inside_charge_weight<=20,aa.waybill_no,null)) as thirteen_waybill_count
       ,count(if(package_inside_charge_weight>10 and package_inside_charge_weight<=20,aa.waybill_no,null))/count(aa.waybill_no) as thirteen_waybill_persent
       ,count(if(package_inside_charge_weight>20,aa.waybill_no,null)) as fourteen_waybill_count
       ,count(if(package_inside_charge_weight>20,aa.waybill_no,null))/count(aa.waybill_no) as fourteen_waybill_persent
    from jms_dwd.dwd_yl_oms_oms_waybill_incre_dt aa
    where dt >=date_add('{{ execution_date | cst_ds }}',-6) and dt<='{{ execution_date | cst_ds }}' and is_delete =1
    group by
       date(input_time)
       ,pick_network_code
       ,pick_network_name
       ,customer_code
       ,customer_name
       ,receiver_province_id
    ) detail
left join jms_dim.dim_network_whole_massage start_network on detail.pick_network_code = start_network.code
left join (select provider_id,provider_desc from jms_dim.dim_network_whole_massage group by provider_id,provider_desc) end_network
    on detail.receiver_province_id =end_network.provider_id;





